package com.example.myapplication15_2;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class StudentDBHelper extends SQLiteOpenHelper {

    // 数据库名称和版本号
    private static final String DATABASE_NAME = "student.db";
    private static final int DATABASE_VERSION = 1;

    // 表名
    public static final String TABLE_STUDENTS = "students";
    
    // 列名
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_NAME = "name";

    // 创建表的SQL语句
    private static final String SQL_CREATE_TABLE =
            "CREATE TABLE " + TABLE_STUDENTS + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_NAME + " TEXT NOT NULL)";

    public StudentDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建数据库表
        db.execSQL(SQL_CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 如果数据库版本升级，删除旧表并重新创建
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENTS);
        onCreate(db);
    }

    /**
     * 添加新的学生到数据库
     * @param name 学生姓名
     * @return 返回插入记录的ID，如果插入失败则返回-1
     */
    public long addStudent(String name) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_NAME, name);
        
        // 插入新行，返回新插入行的ID
        long id = db.insert(TABLE_STUDENTS, null, values);
        db.close();
        return id;
    }

    /**
     * 根据姓名查询学生
     * @param query 查询的学生姓名
     * @return 返回匹配的学生列表
     */
    public List<String> searchStudents(String query) {
        List<String> studentList = new ArrayList<>();
        String selection = COLUMN_NAME + " LIKE ?";
        String[] selectionArgs = { "%" + query + "%" };
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_STUDENTS,   // 表名
                new String[]{COLUMN_ID, COLUMN_NAME},  // 要返回的列
                selection,        // WHERE语句的列
                selectionArgs,    // WHERE语句的值
                null,             // 不分组
                null,             // 不过滤组
                COLUMN_NAME + " ASC"    // 按姓名升序排序
        );

        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID));
                String name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NAME));
                studentList.add(id + ": " + name);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return studentList;
    }

    /**
     * 获取所有学生
     * @return 返回所有学生列表
     */
    public List<String> getAllStudents() {
        List<String> studentList = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_STUDENTS + " ORDER BY " + COLUMN_NAME + " ASC";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID));
                String name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NAME));
                studentList.add(id + ": " + name);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return studentList;
    }
} 